from backend.database.base_db import BaseDB
from backend.models.user import User
from backend.database.base_db import BaseDB

class UserDB(BaseDB):
    def __init__(self):
        super().__init__('user')
    
    def get_user_by_id(self, user_id):
        query = "SELECT * FROM user WHERE user_id = %s"
        result = self.fetch_one(query, (user_id,))
        return User.from_dict(result) if result else None
    
    def get_user_by_username(self, username):
        query = "SELECT * FROM user WHERE username = %s"
        result = self.fetch_one(query, (username,))
        return User.from_dict(result) if result else None
    
    def get_all_users(self):
        query = "SELECT * FROM user"
        results = self.fetch_all(query)
        return [User.from_dict(result) for result in results]
    
    def get_users_by_role(self, role):
        query = "SELECT * FROM user WHERE role = %s"
        results = self.fetch_all(query, (role,))
        return [User.from_dict(result) for result in results]
    
    def add_user(self, user):
        data = user.to_dict()
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        # 如果有user_id，也移除，让数据库自动生成
        if 'user_id' in data:
            del data['user_id']
        
        return self.insert(data)
    
    def update_user(self, user):
        data = user.to_dict()
        user_id = data.pop('user_id', None)
        if user_id is None:
            raise ValueError("用户ID不能为空")
        
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        
        return self.update(data, f"user_id = {user_id}")
    
    def delete_user(self, user_id):
        return self.delete(f"user_id = {user_id}")
    
    def authenticate_user(self, username, password, role):
        """根据用户名、密码和角色验证用户身份"""
        query = "SELECT * FROM user WHERE username = %s AND password = %s AND role = %s"
        result = self.fetch_one(query, (username, password, role))
        return User.from_dict(result) if result else None